
const xlsx = require('node-xlsx');
const fs = require('fs');
const path = require('path');
const mysql = require('mysql');
const progress = require('cli-progress');

const dbConfig = {
  host: '127.0.0.1',
  user: 'root',
  password: 'root',
  database: 'excel_import',
  connectTimeout: 20000
};

const _output = `${__dirname}/result/`;
const tableName = 'xlsxSet';
const splitNumber = 200000;

// 确保输出目录存在
if (!fs.existsSync(_output)) {
  fs.mkdirSync(_output, { recursive: true });
}

function writeExcel (data, fileName) {
  console.time();
  const buffer = xlsx.build([{
    name: "Sheet1",
    data: [Object.keys(data[0])].concat(data.map(Object.values))
  }], {
    '!cols': Object.keys(data[0]).map(() => ({ wch: 20 }))
  });

  fs.writeFileSync(`${_output}${fileName}.xlsx`, buffer);
  console.log(`👌完成导出：${_output}${fileName}.xlsx (${data.length}条)`);
  console.timeEnd();
}

function exportXlsxFromTabel (callback, conn) {
  // 获取总记录数
  conn.query(`SELECT COUNT(*) AS total FROM ${tableName}`, (err, result) => {
    if (err) return callback(err);

    const total = result[0].total;
    const batchCount = Math.ceil(total / splitNumber);
    const bar = new progress.SingleBar({
      format: '导出进度 |{bar}| {percentage}% | {value}/{total} 批',
      barCompleteChar: '\u2588',
      barIncompleteChar: '\u2591'
    });
    bar.start(batchCount, 0);

    let currentBatch = 0;

    function exportBatch () {
      const offset = currentBatch * splitNumber;
      conn.query(
        `SELECT * FROM ${tableName} LIMIT ${offset}, ${splitNumber}`,
        (err, results) => {
          if (err) {
            bar.stop();
            return callback(err);
          }

          writeExcel(results, `${tableName}_part${currentBatch + 1}`);
          bar.update(++currentBatch);

          if (currentBatch < batchCount) {
            exportBatch();
          } else {
            bar.stop();
            callback();
          }
        }
      );
    }

    exportBatch();
  });
}

(function () {
  const conn = mysql.createConnection(dbConfig);
  conn.connect((err) => {
    if (err) {
      console.error('数据库连接失败:', err);
      return;
    }
    console.log('数据库连接成功');
    exportXlsxFromTabel((err) => {
      if (err) console.error(err);
      else console.log('所有数据导出完成');
      conn.end();
    }, conn);
  });
})();
